package edu.must.uh.servlet;

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import edu.must.uh.bean.DeployQueryResultBean;
import edu.must.uh.impl.AttendancdCodeImpl;
import edu.must.uh.impl.AttendancdDaysImpl;
import edu.must.uh.impl.BaseUserExImpl;
import edu.must.uh.util.CellFormat;
import edu.must.uh.util.ParamDto;
import edu.must.uh.util.R;
import edu.must.uh.util.RequestUtil;
import edu.must.uh.util.ServletUtils;
import edu.must.uh.util.SqlUtils;
import edu.must.uh.util.Tools;
import edu.must.uh.util.ToolsOfDate;
import edu.must.uh.util.ToolsOfString;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

@WebServlet("/AttendancdDaysServlet")
public class AttendancdDaysServlet extends BaseServlet {
	
    private static final long serialVersionUID = 12197442526341123L;
  
    AttendancdDaysImpl dataImpl = new AttendancdDaysImpl();    
    
    public Object getList(HttpServletRequest request, HttpServletResponse response) throws Exception {
        
       
        
        List<Map<String,Object>> resList = new ArrayList<Map<String,Object>>();
        Map<String, Object> searchcondition = ServletUtils.getsearchcondition(request);
        String DepartmentId = ToolsOfString.NulltoEmpty(searchcondition.get("DepartmentId"));
        String ATTEND_DATE = ToolsOfString.NulltoEmpty(searchcondition.get("ATTEND_DATE"));
     
        String sqlString  = "SELECT UserId,Account,RealName,DepartmentId FROM base_user WHERE DepartmentId = '"+DepartmentId+"' order by SortCode,Account";
        List<Map<String,Object>> userList = dataImpl.getList(sqlString);
        
        
        if(userList !=null) {
            for (Map<String, Object> userMap : userList) {
                
                Map<String, Object> resUserMap =new HashMap<String, Object>();
                String UserId = ToolsOfString.NulltoEmpty(userMap.get("UserId"));
                String Account = ToolsOfString.NulltoEmpty(userMap.get("Account"));
                String RealName = ToolsOfString.NulltoEmpty(userMap.get("RealName"));
                
                resUserMap.putAll(userMap);
                sqlString  = "SELECT ATTEND_DATE,CODE,PERIOD FROM attendance_days WHERE UserId = '"+UserId+"' and DATE_FORMAT(ATTEND_DATE,'%Y-%m')= '"+ATTEND_DATE+"' order by ATTEND_DATE";
                List<Map<String,Object>> daysList = dataImpl.getList(sqlString);
                if(daysList !=null) {
                    Integer indexDay = 1;
                    for (Map<String, Object> daysMap : daysList) {
                        // String _ATTEND_DATE = ToolsOfString.NulltoEmpty(daysMap.get("ATTEND_DATE"));
                         String CODE = ToolsOfString.NulltoEmpty(daysMap.get("CODE"));                         
                         resUserMap.put("S"+indexDay, CODE);
                         indexDay++;
                        
                    }
                }
                resList.add(resUserMap);
            }
        }
        return R.succ(resList);         
    }
    
    
    public Object UPLOAD(HttpServletRequest request, HttpServletResponse response) throws Exception {
        
        ParamDto dto = RequestUtil.parseParam(request);
        FileItem fileItem =dto.getFileMap().get("file");        
        R  res  = importXlsRpt(fileItem);
        if(res.getCode() == 0) {
            List<Map<String, Object>> list = (List<Map<String, Object>>) res.getData();
            if(!list.isEmpty()){              
                for (Map<String, Object> map : list) {
                    String userId = ToolsOfString.NulltoEmpty(map.get("userId"));
                    String YearMonth = ToolsOfString.NulltoEmpty(map.get("YearMonth"));
                    List<Map<String, Object>> daysList = (List<Map<String, Object>>) map.get("days");
                    String sqlString=  "DELETE FROM attendance_days WHERE UserId = '"+userId +"' AND DATE_FORMAT(ATTEND_DATE,'%Y-%m') ='"+YearMonth+"';";
                    dataImpl.save(sqlString);
                    for(Map<String, Object> dayMap : daysList) {
                        String ATTEND_DATE = ToolsOfString.NulltoEmpty(dayMap.get("ATTEND_DATE"));
                        String CODE = ToolsOfString.NulltoEmpty(dayMap.get("CODE"));
                        String PERIOD = ToolsOfString.NulltoEmpty(dayMap.get("PERIOD"));
                        dataImpl.save(" INSERT INTO attendance_days(UserId,ATTEND_DATE,CODE,PERIOD) VALUES('"+userId+"','"+ATTEND_DATE+"','"+CODE+"','"+PERIOD+"');");
                    }
                    
                }
                              
            }
            
        }else {
           return res; 
        }
        
        
        
        return R.succ();
    }
    
 private String getExcepExcelContents(Sheet sheet,Integer rowInex,Integer ColIndex) {
        
        String reString ="";
        try {
            reString = sheet.getCell(rowInex, ColIndex).getContents().trim();
        }
        catch (Exception e) {
            reString ="";
        }
        return reString;
        
    }
    
    private R  importXlsRpt(FileItem fi)  throws Exception{        
        Workbook workbook = null; 
        List<Map<String, Object>> list =  new ArrayList<Map<String, Object>>(); 
        try{          
            workbook = Workbook.getWorkbook(fi.getInputStream());
            Sheet sheet = workbook.getSheet(0);
            if(sheet.getRows()>1){     
                AttendancdCodeImpl attendancdCodeImpl = new AttendancdCodeImpl();
                List<Map<String,Object>> codelist = attendancdCodeImpl.getlistSelect(null, "");
                Map<String, String> codeMap = new HashMap<String, String>();
                for (Map<String, Object> map : codelist) {
                    codeMap.put(map.get("CODE").toString(), map.get("PERIOD").toString().replace(":","").replace("-",""));
                }
                
                String strYear =getExcepExcelContents(sheet,0, 0);
                String strmonth =getExcepExcelContents(sheet,1, 0);
                strmonth = String.format("%02d",Integer.parseInt(strmonth));
                String strid =getExcepExcelContents(sheet,0, 1).toUpperCase(); 
                String strname = getExcepExcelContents(sheet,1, 1).toUpperCase(); 
                if(!Tools.isNumeric(strYear)) {                    
                    return   R.fail("格式不正确,A1 不是數字");                   
                }
                if(!Tools.isNumeric(strmonth)) {                   
                   
                    return   R.fail("格式不正确,B1 不是數字");
                }
                if(!strid.equals("賬號")) {
                    return   R.fail("格式不正确,A2 不是賬號");
                }
                else if(!strname.equals("姓名")) {   
                    return   R.fail("格式不正确,B2 不是姓名");
                } 
                Integer rowsInteger = sheet.getRows();
                
                BaseUserExImpl baseUserExImpl = new BaseUserExImpl();
               
                for(int i=2; i<rowsInteger; i++){                        
                    String account = sheet.getCell(0, i).getContents().trim();                    
                    Map<String, Object> userDBMap =  baseUserExImpl.getUserByUserName(account);
                    String userId = "";
                     if(userDBMap != null ) {
                        userId =ToolsOfString.NulltoEmpty(userDBMap.get("UserId")) ; 
                    }
                    if(Tools.notEmpty(userId)) {
                       Integer daysconts= ToolsOfDate.getDaysOfMonth(Integer.parseInt(strYear)  , Integer.parseInt(strmonth));
                       Integer day = 0; 
                       
                        Map<String, Object> userMap = new HashMap<String, Object>(); 
                        userMap.put("userId", userId);
                        userMap.put("YearMonth", strYear +"-" + strmonth);
                        List<Map<String, Object>> daysList =  new ArrayList<Map<String, Object>>(); 
                        for(int j=0; j<daysconts; j++){                             
                            
                            Map<String, Object> dayMap = new HashMap<String, Object>();
                            String CODE =ToolsOfString.NulltoEmpty(getdays(sheet,j+2,i)) ;
                            if(CODE.equals("")) {
                                CODE = "OFF";                               
                            } 
                            if(Tools.isNumeric(CODE)) { //Excel 有時候去掉前面的額0
                                CODE =  String.format("%08d",Integer.parseInt(CODE));                          
                            } 
                            day++;  
                            dayMap.put("ATTEND_DATE", strYear +"-" + strmonth +"-"+ String.format("%02d", day)); 
                            dayMap.put("CODE",CODE);
                            dayMap.put("PERIOD",codeMap.get(CODE));
                           
                            daysList.add(dayMap);
                        }
                        userMap.put("days", daysList);
                        list.add(userMap);
                    }                        
                }
                
            }else {               
                return R.fail("Excel 內容為空"); 
            }  
           
        }finally{
            if(workbook!=null){
                workbook.close();
            }        
        }
        return  R.succ(list);
    } 
    
    private String getdays(Sheet sheet,Integer col, Integer index) {
        String reString ="";
        try {
            reString = ToolsOfString.NulltoEmpty(sheet.getCell(col, index).getContents()).trim();  
        }
        catch (Exception e) {
            // TODO: handle exception
        }
        return reString;
    }
    
    public Object download(HttpServletRequest request, HttpServletResponse response) throws Exception { 
        Map<String, Object> searchcondition = ServletUtils.getsearchcondition(request);
        String ATTEND_DATE =ToolsOfString.NulltoEmpty(searchcondition.get("ATTEND_DATE"));        
        String DepartmentId =ToolsOfString.NulltoEmpty(searchcondition.get("DepartmentId")); 
        
        String year ="";
        String month="";
        String[] ATTEND_DATEs=ATTEND_DATE.split("-");
        if(ATTEND_DATEs.length == 2)
        {
            year = ATTEND_DATEs[0];
            month= ATTEND_DATEs[1];
        }
        
        int days= ToolsOfDate.getDaysOfMonth(Integer.parseInt(year)  , Integer.parseInt(month));
        
        
        String sqlString = "SELECT  UserId, Account,RealName FROM v_base_user Where DeleteMark= 0 and DepartmentId = '"+DepartmentId+"' order by SortCode , Account";
        List<Map<String,Object>> userList =  SqlUtils.getMySqlInstance().getlist(sqlString);
        
        
        
        response.reset();
        response.setContentType("application/octet-stream");
        response.addHeader("Content-Disposition" , "attachment ; filename = Attendance_Days.xls " );
    
        try{
           
            OutputStream os = response.getOutputStream();
            WritableWorkbook workbook = Workbook.createWorkbook(os);
            WritableSheet worksheet= workbook.createSheet("Data", 0); 
          
            worksheet.addCell(new Label(0, 0, year, CellFormat.getHeaderFormat12()));
            worksheet.addCell(new Label(1, 0, month, CellFormat.getHeaderFormat12()));
            
            worksheet.addCell(new Label(0, 1, "賬號", CellFormat.getHeaderFormat12()));
            worksheet.addCell(new Label(1, 1, "姓名", CellFormat.getHeaderFormat12())); 
            int  colInteger = 2; 
            int rowIndex =0; 
            
            for (int i = 0; i < days; i++) {
                String dayString = String.valueOf(i+ 1);
                String week =ToolsOfDate.getDayOfWeekByDate(year+"-"+month+"-"+dayString,"E");                
                worksheet.addCell(new Label(colInteger, 0, dayString, CellFormat.getHeaderFormat12()));
                worksheet.addCell(new Label(colInteger++, 1, week, CellFormat.getHeaderFormat12()));
            } 
            
            if(!userList.isEmpty()) {                
                rowIndex = 2;
                for (Map<String,Object> map : userList) {                    
                    worksheet.addCell(new Label(0, rowIndex,ToolsOfString.NulltoEmpty(map.get("Account")), CellFormat.getHeaderFormat12()));
                    worksheet.addCell(new Label(1, rowIndex,ToolsOfString.NulltoEmpty(map.get("RealName")), CellFormat.getHeaderFormat12()));
                 
                    /*   Integer tcolInteger = 2;
                   List<Map<String, String>> scheduling= overtimeApprovalSettingImpl.getScheduling(conn, conn2, userListBean.getUsercode().trim(), year, month);
                    if(!scheduling.isEmpty()){
                        for (int i = 0; i < scheduling.size(); i++) {
                            Map<String, String> schedulingcodeMap = scheduling.get(i);
                            worksheet.addCell(new Label(tcolInteger++, rowIndex, schedulingcodeMap.get("code"), CellFormat.getHeaderFormat12()));
                        }
                    }*/
                    rowIndex++;
                }
            } 
            
            
            workbook.write();
            workbook.close();
            os.flush();
            os.close();
           
        }catch(Exception e){
            e.printStackTrace();
        }    
        return null;
    }
    
    public Object getOneData(HttpServletRequest request, HttpServletResponse response) throws Exception { 
    	String keyValue =ToolsOfString.NulltoEmpty(request.getParameter("ID")); 
    	String sqlString = "SELECT  CODE, REMARK,ISNIGHT,HOURS,TYPE,PERIOD FROM attendance_code Where DELETEMARK = 0  ";
    	sqlString = sqlString + " AND ID = '"+keyValue+"'";
        Map<String,Object>  map = dataImpl.getMap(sqlString);       
        return R.succ(map);         
    }
    
    public Object delete(HttpServletRequest request, HttpServletResponse response) throws Exception {      
            
        String keyValue =ToolsOfString.NulltoEmpty(request.getParameter("ID")); 
        Integer resInteger = dataImpl.delete(keyValue);
        return R.succ(resInteger);         
    }
    
  public Object save(HttpServletRequest request, HttpServletResponse response) throws Exception {  
      		
		String sql = "";
		Map<String, Object> searchcondition = ServletUtils.getsearchcondition(request);
	    String ID =ToolsOfString.NulltoEmpty(searchcondition.get("ID"));
        String CODE =ToolsOfString.NulltoEmpty(searchcondition.get("CODE")); 
      
        String ISNIGHT =ToolsOfString.NulltoEmpty(searchcondition.get("ISNIGHT"));
        String HOURS =ToolsOfString.NulltoEmpty(searchcondition.get("HOURS"));
        String TYPE =ToolsOfString.NulltoEmpty(searchcondition.get("TYPE"));
        String PERIOD =ToolsOfString.NulltoEmpty(searchcondition.get("PERIOD"));
        
        String REMARK =ToolsOfString.NulltoEmpty(searchcondition.get("REMARK"));
        REMARK =  java.net.URLDecoder.decode(REMARK, "UTF-8");
       
        if(Tools.notEmpty(ID)) {
        	sql = "UPDATE attendance_code  SET CODE = '"+CODE+"',REMARK = '"+REMARK+"',ISNIGHT = '" +ISNIGHT+"',HOURS = '" +HOURS+"',TYPE = '" +TYPE+"',PERIOD = '" +PERIOD+"'";
        	sql = sql + " WHERE ID ='"+ID+"'"; 
        }else {
            ID = Tools.getUUID32();
        	sql = "INSERT INTO attendance_code(ID,CODE,REMARK,ISNIGHT,HOURS,TYPE,PERIOD)VALUES";
        	sql = sql + " ('"+ID+"','"+CODE+"','"+REMARK+"','"+ISNIGHT+"','"+HOURS+"','"+TYPE+"','"+PERIOD+"')"; 
        }
        Integer resInteger = dataImpl.save(sql);
        if(resInteger == 1) {
        	   return R.succ(); 
        }
        return R.fail();
       
    }
    
    
    
   
}
